************************************************************************************************
************************************************************************************************
************************************************************************************************
**             																				  **
**																							  **
** REVEALING "MAFIA INC."? FINANCIAL CRISIS, ORGANIZED CRIME AND THE BIRTH OF NEW ENTERPRISES **
**																							  **
** 									    (BUILDING DATASET)								      **
**																							  **
************************************************************************************************
************************************************************************************************
************************************************************************************************

* NOTE:	Run on STATA 15 for MacOS Mojave and Windows 10

*________________________________________ SETTING WORKING DIRECTORY
global dirin "PUT HERE THE DIRECTORY OF THE FOLDER RAW_DATA"
global dirout "PUT HERE THE DIRECTORY OF THE FOLDER CLEANED_DATA"



*________________________________________ OPENING LOG FILE
log using ${dirout}Building_sample.log, replace


*________________________________________ CLEANING DATA
** NUTS CODES
import excel ${dirin}nuts_codes.xls, sheet("Foglio1") firstrow clear
rename  Codiceprovincia cod_prov
rename  CodiceNUTS32006 cod_prov_nuts
rename Denominazioneprovincia name_prov
label var name_prov "Province name"
label var name_reg "Region name"
label var cod_prov "Province code (ISTAT)"
label var cod_prov_nuts "Province code (NUTS3)"
replace  name_prov=lower(name_prov)
replace  name_reg=lower(name_reg)
destring, replace
save ${dirout}nuts_codes.dta, replace


** DEMOGRAPHY OF ENTERPRISES
import excel ${dirin}dem_ent.xlsx, sheet("Foglio1") firstrow clear
reshape long nate morte registrate, i(province) j(year)
rename nate new
rename morte closed
rename registrate registered
rename  province name_prov
replace name_prov=lower(name_prov)
save ${dirout}dem_ent.dta, replace


** ENTERPRISES CONSTRUCTION SECTOR
import excel ${dirin}construction.xls, sheet("Foglio1") firstrow clear
destring, replace
rename nome_prov name_prov
replace  name_prov=lower(name_prov)
reshape long edil, i(name_prov) j(year)
rename edil construction
save ${dirout}construction.dta, replace


** ENTERPRISES R&D SECTOR
import excel ${dirin}research.xlsx, sheet("Foglio1") firstrow clear
destring, replace
rename nome_prov name_prov
replace  name_prov=lower(name_prov)
reshape long ricerca, i(name_prov) j(year)
rename ricerca research
save ${dirout}research.dta, replace


** ENTERPRISES LEGAL FORM
import excel ${dirin}legal_form.xls, sheet("Foglio1") firstrow clear
destring, replace
rename nome_prov name_prov
replace  name_prov=lower(name_prov)
reshape long sdc sdp ii other srl, i(name_prov) j(year)
keep sdc name_prov year
rename sdc limited
save ${dirout}legal_form.dta, replace


** TMI
import excel ${dirin}tmi.xlsx, sheet("Foglio1") firstrow clear
destring, replace
rename  name_pr name_prov
drop if name_prov=="Ogliastra" | name_prov=="Carbonia-Iglesias" | name_prov=="Olbia-Tempio" | name_prov=="Medio Campidano"
sum mafia_index_ts  if name_reg!="Campania" & name_reg!="Calabria" & name_reg!="Sicilia" 
gen tmi =((mafia_index_ts -abs(r(min)))/(r(max)-r(min))) if name_reg!="Campania" & name_reg!="Calabria" & name_reg!="Sicilia" 
sum mafia_index_ts
gen tmi_all =((mafia_index_ts -abs(r(min)))/(r(max)-r(min))) 
drop mafia_index_ts name_reg
replace name_prov=lower(name_prov)
save ${dirout}tmi.dta, replace


** TMI PRE
import excel ${dirin}tmi_pre.xlsx, sheet("Foglio1") firstrow clear
rename nome_prov name_prov
replace name_prov=lower(name_prov)
replace name_reg=lower(name_reg)
sum tmi_2009  if name_reg!="campania" & name_reg!="calabria" & name_reg!="sicilia" 
gen tmi_pre =((tmi_2009 -abs(r(min)))/(r(max)-r(min))) if name_reg!="campania" & name_reg!="calabria" & name_reg!="sicilia" 
drop tmi_2009
save ${dirout}tmi_pre.dta, replace


** PSI
import excel ${dirin}psi.xls, sheet("Foglio2") firstrow clear
rename Province name_prov
replace name_prov=lower(name_prov)
destring, replace
gen psi=3 if  (Associazionemafiosa>=1 & Omicidipermafia>=1 & Estorsioni>=1) 
replace psi=2 if  (Associazionemafiosa>=1 & Omicidipermafia>=1 & Estorsioni<1) | (Associazionemafiosa>=1 & Omicidipermafia<1 & Estorsioni>=1) | (Associazionemafiosa<1 & Omicidipermafia>=1 & Estorsioni>=1)
replace psi=1 if  (Associazionemafiosa<1 & Omicidipermafia>=1 & Estorsioni<1) | (Associazionemafiosa>=1 & Omicidipermafia<1 & Estorsioni<1) | (Associazionemafiosa<1 & Omicidipermafia<1 & Estorsioni>=1)
replace psi=0 if  (Associazionemafiosa<1 & Omicidipermafia<1 & Estorsioni<1) & (Numerobeniconfiscati<1 & Scioglimenticonsiglicomunali<1)
keep name_prov psi
save ${dirout}psi.dta, replace


** ART. 416 BIS
import excel ${dirin}art416_bis.xlsx, sheet("reati") firstrow clear
replace nome_prov=lower(nome_prov)
rename  nome_prov name_prov
reshape long  associazione_mafiosa, i(name_prov) j(year)
keep name_prov associazione_mafiosa year
rename associazione_mafiosa art416_bis
save ${dirout}art416_bis.dta, replace


** CONFINO LAW
use ${dirin}confino.dta, clear
rename nome_prov name_prov
rename confinati confined
save ${dirout}confino.dta, replace


** TOURISM
import excel ${dirin}tourism.xlsx, sheet("Foglio1") firstrow clear
reshape long turismo_, i(Province) j(year)
rename turismo_ tourism
rename Province name_prov
replace  name_prov=lower(name_prov)
save ${dirout}tourism.dta, replace


** WASTES
import excel ${dirin}wastes.xlsx, sheet("Foglio1") firstrow clear
rename nome_prov name_prov
replace  name_prov=lower(name_prov)
reshape long rifiuti_tot, i(name_prov) j(year)
rename rifiuti_tot wastes
save ${dirout}wastes.dta, replace


** BANKS
use ${dirin}banks.dta, clear
foreach var in n_sportelli_minori n_sportelli_piccole n_sportelli_medie n_sportelli_grandi n_sportelli_maggiori  {
replace  `var'=0 if `var'==.
}
gen big_banks=(n_sportelli_grandi +n_sportelli_maggiori)/(n_sportelli_minori +n_sportelli_piccole +n_sportelli_medie+ n_sportelli_grandi +n_sportelli_maggiori)
keep name_prov cod_prov_nuts year big_banks
save ${dirout}banks.dta, replace


** CREDITS
use ${dirin}credits.dta, clear
drop code_pro
rename nome_prov name_prov 
rename imp_tot credit_tot
save ${dirout}credits.dta, replace


** NEWSPAPERS
use ${dirin}newspapers.dta, clear
rename quotidiani newspapers
save ${dirout}newspapers.dta, replace


** SELF-EMPLOYED
import excel ${dirin}self_emp.xlsx, sheet("Foglio1") firstrow clear
replace  nome_prov=lower(nome_prov)
rename nome_prov name_prov
reshape long perc_autonomi, i(name_prov) j(year)
rename perc_autonomi self_emp
save ${dirout}self_emp.dta, replace


** BLOOD
import excel ${dirin}blood.xlsx, sheet("sangue") firstrow clear
rename nome_reg name_reg
replace  name_reg=lower(name_reg)
reshape long sangue, i(name_reg) j(year)
rename sangue blood
save ${dirout}blood.dta, replace


** TRIALS
import excel ${dirin}trials.xlsx, sheet("Foglio2") firstrow clear
destring, replace
rename nome_prov name_prov
replace  name_prov=lower(name_prov)
reshape long fallimento, i(name_prov) j(year)
rename fallimento trial
save ${dirout}trials.dta, replace


** UNEMPLOYMENT RATE
import excel ${dirin}un_rate.xlsx, sheet("Foglio2") firstrow clear
destring, replace
rename nome_prov name_prov
replace  name_prov=lower(name_prov)
reshape long un_rate, i(name_prov) j(year)
save ${dirout}un_rate.dta, replace


** TOTAL POPULATION
import excel ${dirin}population.xlsx, sheet("popolazione_totale") firstrow clear
rename nome_prov name_prov
replace  name_prov=lower(name_prov)
reshape long popolazione_totale, i(name_prov) j(year)
rename popolazione_totale pop_tot
save ${dirout}pop_tot.dta, replace


** URBAN POPULATION
import excel ${dirin}population.xlsx, sheet("popolazione_urbana") firstrow clear
rename nome_prov name_prov
replace  name_prov=lower(name_prov)
reshape long popolazione_urbana, i(name_prov) j(year)
rename popolazione_urbana popurban
save ${dirout}pop_urb.dta, replace


** GDP
import excel ${dirin}gdp_xc.xlsx, sheet("Data") firstrow clear
rename nome_prov name_prov
replace  name_prov=lower(name_prov)
reshape long gdp, i(name_prov) j (year)
rename gdp gdp_xc
destring, replace
save ${dirout}gdp_xc.dta, replace


** DSP
import excel ${dirin}dsp.xls, sheet("Foglio1") firstrow clear
gen dps_01= pop_censo01>5000
gen dps_11= pop_censo11>5000
collapse dps_01 dps_11, by(cod_prov)
save ${dirout}dsp.dta, replace


** EXPORTS
use ${dirin}exports.dta, clear
keep nome_prov year tot_exp
rename nome_prov name_prov
save ${dirout}exports.dta, replace


** PROCUREMENTS
use ${dirin}procurement.dta, clear
keep year nome_prov importo_iniziale
rename nome_prov name_prov
rename importo_iniziale procurement
save ${dirout}procurement.dta, replace


** EU FUNDS
use ${dirin}eu_funds.dta, clear
drop code_prov
rename nome_prov name_prov
rename tot_pagamenti_eu fund_eu
save ${dirout}eu_funds.dta, replace


** PROVINCE COORDINATES
use ${dirin}prov_db.dta, clear
keep nome_prov x_centr_ y_centr_ id_uniq
rename nome_prov name_prov
rename id_uniq _ID
rename x_centr_ longitude
rename y_centr_ latitude
save ${dirout}prov_db.dta, replace


*________________________________________ MERGING DATA
use ${dirout}nuts_codes.dta, clear

merge 1:1 name_prov using ${dirin}surface.dta
keep if _m==3
drop _m

merge 1:m name_prov using ${dirout}dem_ent.dta
keep if _m==3
drop _m

merge m:m name_prov year using ${dirout}construction.dta
keep if _m==3
drop _m

merge m:m name_prov year using ${dirout}research.dta
keep if _m==3
drop _m

merge m:m name_prov year using ${dirout}legal_form.dta
keep if _m==3
drop _m

merge m:1 name_prov using ${dirout}tmi.dta
keep if _m==3
drop _m

merge m:1 name_prov using ${dirout}tmi_pre.dta
keep if _m==3
drop _m

merge m:1 name_prov using ${dirout}psi.dta
keep if _m==3
drop _m

merge m:1 name_prov using ${dirout}confino.dta
drop _m

merge m:m name_prov year using ${dirout}art416_bis.dta
keep if _m==3
drop _m

merge m:m name_prov year using ${dirout}tourism.dta
keep if _m==3
drop _m

merge m:m name_prov year using ${dirout}wastes.dta
keep if _m==3
drop _m

merge m:m name_prov year using ${dirout}banks.dta
keep if _m==3
drop _m

merge m:m name_prov year using ${dirout}credits.dta
keep if _m==3
drop _m

merge m:m name_prov year using ${dirout}self_emp.dta
keep if _m==3
drop _m

merge m:m name_reg year using ${dirout}blood.dta
keep if _m==3
drop _m

merge m:m name_prov year using ${dirout}trials.dta
keep if _m==3
drop _m

merge m:m name_prov year using ${dirout}newspapers.dta
keep if _m==3
drop _m

merge m:m name_prov year using ${dirout}un_rate.dta
keep if _m==3
drop _m

merge m:m name_prov year using ${dirout}pop_tot.dta
keep if _m==3
drop _m

merge m:m name_prov year using ${dirout}pop_urb.dta
keep if _m==3
drop _m

merge m:m name_prov year using ${dirout}gdp_xc.dta
keep if _m==3
drop _m

merge m:1 cod_prov using ${dirout}dsp.dta
keep if _m==3
drop _m

merge m:m name_prov year using ${dirout}exports.dta
keep if _m==3
drop _m

merge m:m name_prov year using ${dirout}procurement.dta
keep if _m==3
drop _m

merge m:m name_prov year using ${dirout}eu_funds.dta
drop if _m==2
drop _m

append using ${dirin}dem_ent_extended.dta


merge m:1 name_prov using ${dirout}prov_db.dta
keep if _m==3
drop _m

merge m:1 name_prov using ${dirin}prov_coord_180.dta
keep if _m==3
drop _m




*________________________________________ VARIABLES CONSTRUCTION


*_________________ OUTCOMES
gen new_std= (new/pop_tot)*100000
gen new_std_ln=ln(new_std)
label var new_std "New enterprises (100,000 inh.)"
label var new_std_ln "New enterprises"
drop new

gen construction_std= (construction/pop_tot)*100000
gen construction_std_ln=ln(construction_std)
label var construction_std_ln "Construction"
drop construction

gen ric_std= (research/pop_tot)*100000
gen ric_std_ln=ln(ric_std)
label var ric_std_ln "Research"
drop research 

gen limited_std= (limited/pop_tot)*100000
gen limited_std_ln=ln(limited_std)
label var limited_std_ln "Lim. Liab."
drop limited

gen closed_std= (closed/pop_tot)*100000
gen closed_std_ln=ln(closed_std)
label var closed_std_ln "Closed enterprises"
drop closed

gen registered_std= (registered/pop_tot)*100000
gen registered_std_ln=ln(registered_std)
label var registered_std_ln "Registered enterprises"
drop registered

gen proc_std=((procurement/pop_tot)*100000)/1000
gen proc_std_ln=ln(proc_std)
label var proc_std_ln "Public proc."
drop procurement

gen ln_gdp=ln(gdp_xc)
label var ln_gdp "GDP per capita (ln)"

gen tot_exp_r=((tot_exp*1000)/(gdp*pop_tot))
label var tot_exp_r "Export/GDP"
drop tot_exp 

gen flag=name_reg!="campania" & name_reg!="calabria" & name_reg!="sicilia"
bysort year flag: egen tot_fund_eu=sum(fund_eu) if flag==1
gen fund_eu_std= fund_eu/tot_fund_eu
label var fund_eu_std "EU funds"
drop flag fund_eu tot_fund_eu 

replace un_rate=un_rate/100
label var un_rate "Unemp. rate"


*_________________ CRISIS
gen crisis=year>2006
label var crisis "Crisis"
label var year "Year"


*_________________ TMI
bysort name_prov: egen tmi_e=max(tmi)
drop tmi
label var tmi_e "TMI"

** Tertiles
xtile tmi_q3=tmi_e if name_reg!="campania" & name_reg!="calabria" & name_reg!="sicilia" , n(3)
label define tmi_q3 1"Low Mafia" 2"Mid. Mafia" 3"High Mafia"
label value tmi_q3 tmi_q3
label var tmi_q3 "Tertiles of TMI"

gen mafia_n3=1 if  tmi_q3==3 
replace mafia_n3=0 if  tmi_q3<3 
label var mafia_n3 "Mafia"
label define mafia_n3 0 "No Mafia" 1 "Mafia"
label value mafia_n3 mafia_n3

gen crisis_mafia_n3_int=crisis*mafia_n3
label var crisis_mafia_n3_int "MafiaXCrisis"

** Quartiles
xtile tmi_q4=tmi_e if name_reg!="campania" & name_reg!="calabria" & name_reg!="sicilia" , n(4)
label var tmi_q4 "Quartiles of TMI"

gen mafia_n4=1 if  tmi_q4==4 
replace mafia_n4=0 if  tmi_q4<4 
label var mafia_n4 "Mafia"
label define mafia_n4 0 "No Mafia" 1 "Mafia"
label value mafia_n4 mafia_n4

gen crisis_mafia_n4_int=crisis*mafia_n4
label var crisis_mafia_n4_int "MafiaXCrisis"

** Quintiles
xtile tmi_q5=tmi_e if name_reg!="campania" & name_reg!="calabria" & name_reg!="sicilia" , n(5)
label var tmi_q5 "Quintiles of TMI"

gen mafia_n5=1 if  tmi_q5==5
replace mafia_n5=0 if  tmi_q5<5 
label var mafia_n5 "Mafia"
label define mafia_n5 0 "No Mafia" 1 "Mafia"
label value mafia_n5 mafia_n5

gen crisis_mafia_n5_int=crisis*mafia_n5
label var crisis_mafia_n5_int "MafiaXCrisis"

** Continuous
gen tmi_cont=tmi_e*100 if name_reg!="campania" & name_reg!="calabria" & name_reg!="sicilia"
label var tmi_cont "Mafia"

gen crisis_mafia_cont_int=crisis*tmi_cont
label var crisis_mafia_cont_int "MafiaXCrisis"


*_________________ TMI PRE
bysort name_prov: egen tmi_pre_e=mean(tmi_pre)
drop tmi_pre
label var tmi_pre_e "TMI Pre"

** Tertiles
xtile tmi_q3_pre=tmi_pre_e if name_reg!="campania" & name_reg!="calabria" & name_reg!="sicilia", n(3)
label var tmi_q3_pre "Tertiles of TMI Pre"

gen mafia_n3_pre=1 if  tmi_q3_pre==3
replace mafia_n3_pre=0 if  tmi_q3_pre<3
label var mafia_n3_pre "Mafia"

gen crisis_mafia_n3_pre_int=crisis*mafia_n3_pre
label var crisis_mafia_n3_pre_int "MafiaXCrisis"

** Quartiles
xtile tmi_q4_pre=tmi_pre_e if name_reg!="campania" & name_reg!="calabria" & name_reg!="sicilia", n(4)
label var tmi_q4_pre "Quartiles of TMI Pre"

gen mafia_n4_pre=1 if  tmi_q4_pre==4
replace mafia_n4_pre=0 if  tmi_q4_pre<4
label var mafia_n4_pre "Mafia"

gen crisis_mafia_n4_pre_int=crisis*mafia_n4_pre
label var crisis_mafia_n4_pre_int "MafiaXCrisis"

** Quintiles
xtile tmi_q5_pre=tmi_pre_e if name_reg!="campania" & name_reg!="calabria" & name_reg!="sicilia", n(5)
label var tmi_q5_pre "Quintiles of TMI Pre"

gen mafia_n5_pre=1 if  tmi_q5_pre==5
replace mafia_n5_pre=0 if  tmi_q5_pre<5
label var mafia_n5_pre "Mafia"

gen crisis_mafia_n5_pre_int=crisis*mafia_n5_pre
label var crisis_mafia_n5_pre_int "MafiaXCrisis"

** Continuous
gen tmi_cont_pre=tmi_pre_e*100 if name_reg!="campania" & name_reg!="calabria" & name_reg!="sicilia"
label var tmi_cont_pre "Mafia"

gen crisis_mafia_cont_pre_int=crisis*tmi_cont_pre
label var crisis_mafia_cont_pre_int "MafiaXCrisis"


*_________________ TMI -- Excluding also Apulia
xtile tmi_q3_nopuglia=tmi_e if name_reg!="campania" & name_reg!="calabria" & name_reg!="sicilia" & name_reg!="puglia", n(3)
label var tmi_q3_nopuglia "Tertiles of TMI (no Apulia)"

gen mafia_n3_nopuglia=1 if  tmi_q3_nopuglia==3 
replace mafia_n3_nopuglia=0 if  tmi_q3_nopuglia<3 
label var mafia_n3_nopuglia "Mafia"

gen crisis_mafia_n3_nopuglia_int=crisis*mafia_n3_nopuglia
label var crisis_mafia_n3_nopuglia "MafiaXCrisis"


*_________________ TMI -- All provinces
bysort name_prov: egen tmi_all_e=max(tmi_all)
drop tmi_all
label var tmi_all_e "TMI (All. Prov.)"

xtile tmi_q3_all=tmi_all_e, n(3)
label var tmi_q3_all "Tertiles of TMI (All prov.)"

gen mafia_n3_all=1 if  tmi_q3_all==3
replace mafia_n3_all=0 if  tmi_q3_all<3
label var mafia_n3_all "Mafia"

gen crisis_mafia_n3_all_int=crisis*mafia_n3_all
label var crisis_mafia_n3_all "MafiaXCrisis"


*_________________ PSI
gen psi_e=1 if  psi>0 & name_reg!="campania" & name_reg!="calabria" & name_reg!="sicilia"
replace psi_e=0 if  psi==0 & name_reg!="campania" & name_reg!="calabria" & name_reg!="sicilia"
drop psi
label var psi_e "Mafia"
gen crisis_mafia_psi=crisis*psi_e
label var crisis_mafia_psi "MafiaXCrisis"


*_________________ ART. 416 bis
bysort name_prov: egen mean_ass=mean(art416_bis) 
drop art416_bis
replace mean_ass=round(mean_ass)

gen mafia_ass=1 if mean_ass>0 & name_reg!="campania" & name_reg!="calabria" & name_reg!="sicilia" 
replace mafia_ass=0 if mean_ass==0 & name_reg!="campania" & name_reg!="calabria" & name_reg!="sicilia" 
drop mean_ass
label var mafia_ass "Mafia"

gen crisis_mafia_ass_int=crisis*mafia_ass
label var crisis_mafia_ass_int "MafiaXCrisis"


*_________________ CONTROLS

gen credit_ln=ln((credit_tot/pop_tot)*100000)
label var credit_ln "Total credits (100,000 inh., Ln)"

gen pop_urb=popurban/pop_tot
drop popurban
label var pop_urb "Urban population (perc.)"

replace newspapers=newspapers/(pop_tot/1000)
gen newspapers_ln=ln(newspapers)
label var newspapers "Newspaper circulation (1,000 inh.)"
label var newspapers_ln "Newspaper circulation (1,000 inh., Ln)"

gen wastes_xc=wastes/pop_tot
drop wastes
label var wastes_xc "Wastes per capita (tons)"

gen south=(name_reg=="basilicata" | name_reg=="calabria" | name_reg=="campania" | name_reg=="puglia" | name_reg=="sicilia" | name_reg=="sardegna" | name_reg=="molise" | name_reg=="abruzzo")
gen centre=(name_reg=="lazio" | name_reg=="marche" | name_reg=="toscana" | name_reg=="umbria")
gen north_east=(name_reg=="emilia-romagna" | name_reg=="veneto" | name_reg=="trentino-alto adige" | name_reg=="friuli-venezia giulia")
gen north_west=(name_reg=="piemonte" | name_reg=="liguria" | name_reg=="lombardia" | name_reg=="valle d'aosta/vall�e d'aoste")
gen north=(north_east==1 | north_west==1)
label var south "Southern Italy"
label var north "Northern Italy"
label var centre "Central Italy"
label var north_east "North-eastern Italy"
label var north_west "North-western Italy"

gen dsp=1 if dps_01==1 & year<=2011
replace dsp=1 if dps_11==1 & year>2011
replace dsp=0 if dsp==.
drop dps_01 dps_11
label var dsp "Domestic Stability Pact"


label var big_banks "Big banks (perc.)"
label var tourism "Tourism index"
label var self_emp "Self-employed (perc.)"
label var trial "Trial duration (days)"
label var blood "Blood donations (Bags per 100 inh.)"

label var pop_tot "Population"
label var surface "Surface (Km2)"

*_________________ CONFINO LAW

** Aggregating controls of new provinces back to original provinces
gen noprov=1 if name_prov=="biella" |  name_prov=="lecco" |  name_prov=="lodi" |  name_prov=="prato" |  name_prov=="rimini" |  name_prov=="verbano-cusio-ossola" |  name_prov=="oristano"  

gen f1=name_prov=="biella" |  name_prov=="vercelli" 
gen f2=name_prov=="lecco" |  name_prov=="como" 
gen f3=name_prov=="lodi" |  name_prov=="milano" 
gen f4=name_prov=="prato" |  name_prov=="firenze" 
gen f5=name_prov=="rimini" |  name_prov=="forli-cesena" 
gen f6=name_prov=="verbano-cusio-ossola" |  name_prov=="novara" 
gen f7=name_prov=="oristano" |  name_prov=="cagliari" 

foreach var1 in pop_urb tourism trial wastes_xc big_banks /*
*/  self_emp blood newspapers_ln  {
local varlabel1: var label `var1'
gen `var1'_iv=`var1'
label var `var1'_iv "`varlabel1' (IV)"
foreach var2 in f1 f2 f3 f4 f5 f6 f7 {
bysort `var2' year: egen `var1'_`var2'_iv2=wtmean(`var1') if `var2'==1, weight(surface)
replace `var1'_iv=`var1'_`var2'_iv2 if `var2'==1
}
}
drop f1 f2 f3 f4 f5 f6 f7 *_iv2


** Mafia presence and instrument
replace confined=0 if confined==.
gen co=confined if name_reg!="campania" & name_reg!="calabria" & name_reg!="sicilia"  & tmi_e!=. & noprov!=1
drop confined noprov
label var co "People resettled by confino"
gen instrument=co*crisis
label var instrument "ResettledXCrisis"


*_________________ INTERACTION WITH YEAR DUMMIES -- Reference 2006 (year_10)
label define year 1997 "(t-10)" 1998 "(t-9)" 1999 "(t-8)" 2000 "(t-7)" 2001 "(t-6)" /*
*/ 2002 "(t-5)" 2003 "(t-4)" 2004 "(t-3)" 2005 "(t-2)" 2006 "(t-1)" 2007 "(t)" /*
*/ 2008 "(t+1)" 2009 "(t+2)" 2010 "(t+3)" 2011 "(t+4)" 2012 "(t+5)" 2013 "(t+6)" 
label value year year
ta year, gen(year_)


foreach var of varlist year_1 year_2 year_3 year_4 year_5  year_6 year_7 year_8 year_9 year_10 /*
*/ year_11 year_12 year_13 year_14 year_15 year_16 year_17  {
local varlabel : var label `var'
local newname = subinstr("`varlabel'","year==","",.)
label variable `var' "`newname'"
}

** TMI 
foreach var1 in mafia_n3 {
local varlabel1: var label `var1'
foreach var2 in year_1 year_2 year_3 year_4 year_5  year_6 year_7 year_8 year_9  /*
*/ year_11 year_12 year_13 year_14 year_15 year_16 year_17 {
local varlabel2: var label `var2'
gen `var2'_`var1'=`var2'*`var1'
label var `var2'_`var1' "`varlabel1' x `varlabel2'"
}
}

ta tmi_q3, gen(tmi_q3_)
label var tmi_q3_1 "Low Mafia"
label var tmi_q3_2 "Mid Mafia"
label var tmi_q3_3 "High Mafia"
foreach var1 in tmi_q3_2 tmi_q3_3 {
local varlabel1: var label `var1'
foreach var2 in year_1 year_2 year_3 year_4 year_5  year_6 year_7 year_8 year_9  /*
*/ year_11 year_12 year_13 year_14 year_15 year_16 year_17 {
local varlabel2: var label `var2'
gen `var2'_`var1'=`var2'*`var1'
label var `var2'_`var1' "`varlabel1' x `varlabel2'"
}
}

** CONFINO LAW
foreach var1 in co {
local varlabel1: var label `var1'
foreach var2 in year_7 year_8 year_9  year_11 year_12 year_13 year_14 year_15 year_16 year_17 {
local varlabel2: var label `var2'
gen `var2'_`var1'=`var2'*`var1'
label var `var2'_`var1' "`varlabel1' x `varlabel2'"
}
}

** MACRO REGIONS -- Reference South and Islands
foreach var1 in north centre north_east north_west{
local varlabel1: var label `var1'
foreach var2 in  year_1 year_2 year_3 year_4 year_5  year_6 year_7 year_8 year_9 /*
*/ year_11 year_12 year_13 year_14 year_15 year_16 year_17 {
local varlabel2: var label `var2'
gen `var2'_`var1'=`var2'*`var1'
label var `var2'_`var1' "`varlabel1' x `varlabel2'"
}
}


** CONTROLS - 2006 value
foreach var1 in credit_ln tourism trial wastes_xc big_banks newspapers_ln pop_urb self_emp blood {
local varlabel1: var label `var1'
gen `var1'_flag=`var1' if year==2006
bysort name_prov: egen `var1'_2006=mean(`var1'_flag)
label var `var1'_2006 "`varlabel1'(2006)"
drop `var1'_flag
}


foreach var1 in dsp credit_ln_2006 tourism_2006 trial_2006 wastes_xc_2006 big_banks_2006 /*
*/ newspapers_ln_2006 pop_urb_2006 self_emp_2006 blood_2006 {
local varlabel1: var label `var1'
foreach var2 in year_7 year_8 year_9  year_11 year_12 year_13 year_14 year_15 year_16 year_17 {
gen `var2'_`var1'=`var2'*`var1'
label var `var2'_`var1' "`varlabel1' x `varlabel2'"
}
}


egen id=group(name_prov)
xtset id year


save ${dirout}final_database.dta, replace

log close
